package mis510.homefinders;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

	public class DBUserAdapter
	{
		public static final String KEY_ROWID = "_id";
		public static final String KEY_USERNAME= "Username";
		public static final String KEY_PASSWORD = "Password";
		public static final String KEY_EMAIL = "Email";
		public static final String KEY_NEWSLETTER = "Newsletter";
		public static final String KEY_CLASS = "Class";
		public static final String KEY_PAID = "Paid";
		public static final String KEY_CITY = "City";

		private static final String TAG = "DBAdapter";

		private static final String DATABASE_NAME = "homefinders";
		private static final String DATABASE_TABLE_LOGIN = "login";
		private static final String DATABASE_TABLE_RMREQ = "rmreq";
		private static ArrayList<String> rm_email = new ArrayList<String>();
		private static final int DATABASE_VERSION = 1;

		private static final String DATABASE_CREATE_LOGIN =
			"create table login (Username VARCHAR(20) primary key, "
			+ "Password VARCHAR(10) not null, "
			+ "Email VARCHAR(60) NOT NULL, "
			+ "Newsletter BOOLEAN DEFAULT false, "
			+ "Paid BOOLEAN DEFAULT false); ";
		
		private static final String DATABASE_CREATE_RMREQ =
				"create table rmreq (FirstName varchar(50) NOT NULL, "
				+ "LastName varchar(50) NOT NULL, "
				+ "Email varchar(60) primary key, "
				+ "PhoneNumber varchar(50) NULL, "
				+ "Age varchar(50) NULL, "
				+ "Profession varchar(50) NULL, "
				+ "Address varchar(50) NULL, "
				+ "City varchar(50) NULL, "
				+ "State varchar(50) NULL, "
				+ "Budget varchar(50) NULL, "
				+ "FoodHabits varchar(50) NULL, "
				+ "RoomType varchar(50) NULL, "
				+ "NoOfBed varchar(50) NULL, "
				+ "DateOfJoin varchar(50) NULL, "
				+ "Floor varchar(50) NULL, "
				+ "NoOfRoomates varchar(50) NULL, "
				+ "Gender varchar(50) NULL, "
				+ "AgeGroup varchar(50) NULL, "
				+ "Pets varchar(50) NULL, "
				+ "Class varchar(50) NULL); ";
				

		private Context context = null;
		private DatabaseHelper DBHelper;
		private SQLiteDatabase db;

		public DBUserAdapter(Context ctx)
		{
			this.context = ctx;
			DBHelper = new DatabaseHelper(context);
		}

		private static class DatabaseHelper extends SQLiteOpenHelper
		{
			DatabaseHelper(Context context)
			{
				super(context, DATABASE_NAME, null, DATABASE_VERSION);
			}

			@Override
			public void onCreate(SQLiteDatabase db)
			{
				db.execSQL(DATABASE_CREATE_LOGIN);
				//System.out.println("login created");
				db.execSQL(DATABASE_CREATE_RMREQ);
				//System.out.println("rmreq created");
				db.execSQL("insert into " +
						"login values('Annie','12345','anniefzh@gmail.com','false','true');");
				db.execSQL("insert into " +
						"login values('Bob','67890','bob@email.arizona.edu','false','false');");
				db.execSQL("insert into " +
						"login values('Admin','mis510','mis510@gmail.com','false','true');");
				db.execSQL("insert into "+
						"rmreq values('Surabhi','Mahanty','surabhimahanty@email.arizona.edu','5208888888','18','Student','1425 Park Adams ','Tucson','Arizona','Below 500','Veg','Shared','2','5/1/2013','Ground','1','Female','18-25','Yes','cluster_0');");
				db.execSQL("insert into "+
						"rmreq values('Kavya','Maiya','kmaiya@email.arizona.edu','5208381515','18','Student','817 E Drachman Street','Tucson','Arizona','1000-1500','Veg','Single','3','6/1/2013','Ground','2','Female','25-30','Yes','cluster_0');");
				db.execSQL("insert into "+
						"rmreq values('Surendra Kumar','Govinda Raju','surendrakumar@email.arizona.edu','5208381515','28','Student','1425 Park Adams ','Tucson','Arizona','500-1000','No preference','Shared','1','5/25/2013','High','4','Male','30-40','No Preference','cluster_0');");
				db.execSQL("insert into "+
						"rmreq values('Sandeep','Kulkarni','skulkarni@gmail.com','5208381515','23','Software Engineer','555 E Street','San Diego','California','Above 2000','Meat','Shared','2','5/25/2013','Ground','4','Male','18-25','Yes','cluster_1');");
				db.execSQL("insert into "+
						"rmreq values('Namratha','Karanth','nkaranth@gmail.com','5208381515','38','Student','605 Sea Cliff Avenue','San Diego','California','1500-2000','No preference','Shared','2','5/25/2013','High','1','Male','18-25','Yes','cluster_1');");
				db.execSQL("insert into "+
						"rmreq values('Madhavi','Kemba','madhavik@gmail.com','5208381515','36','Student','559 35th Avenue','San Diego','California','1500-2000','No preference','Shared','4','5/1/2013','Ground','2','Male','18-25','Yes','cluster_1');");
				db.execSQL("insert into "+
						"rmreq values('Nikitha','Bhat','nikithabhat@gmail.com','5208381515','35','Professor','57 Emerald Garden Ln','Houston','Texas','1500-2000','Veg','Single','1','7/1/2013','Ground','4','Male','30-40','No Preference','cluster_2');");
				db.execSQL("insert into "+
						"rmreq values('Akshay','Kulkarni','akulkarni@yahoo.com','5208381515','26','Professor','1765 Michigan Ave','Chicago','Illinois','1500-2000','No preference','Shared','2','5/1/2013','Ground','1','Male','30-40','No Preference','cluster_2');");
				db.execSQL("insert into "+
						"rmreq values('Jimy','Matthews','jimywimy@gmail.com','5208381515','22','Business Analyst','3511 Fremont Street','Chicago','Illinois','1500-2000','No preference','Shared','2','5/1/2013','Medium','5','Male','18-25','Yes','cluster_3');");
				db.execSQL("insert into "+
						"rmreq values('Sujith','Shivanandan','sujiths@yahoo.com','5208381515','28','Business Analyst','45 E End Ave','Chicago','Illinois','1000-1500','Veg','Shared','2','5/1/2013','Ground','4','Male','18-25','Yes','cluster_3');");
				db.execSQL("insert into "+
						"rmreq values('Shantala','Hiremath','shantalahiremath@yahoo.com','5208381515','32','Business Analyst','5512 Ashmore Dr','Houston','Texas','Below 500','Veg','Shared','1','5/1/2013','High','5','Male','30-40','No Preference','cluster_3');");
				db.execSQL("insert into "+
						"rmreq values('Anjali','Mahanty','anjalimahanty@gmail.com','5208381515','21','Business Analyst','78 Hidden Oaks Ln','Houston','Texas','Above 2000','Meat','Single','3','5/1/2013','High','4','Male','18-25','No','cluster_3');");
				db.execSQL("insert into "+
						"rmreq values('Pushkar','Gejji','pushkarg@gmail.com','5208381515','30','Software Engineer','1245 Skyview Green Dr','Houston','Texas','500-1000','Veg','Shared','2','5/1/2013','Ground','2','Female','18-25','Yes','cluster_4');");
				db.execSQL("insert into "+
						"rmreq values('Sukrut','Gejji','sukrutgejji@yahoo.com','5208381515','20','Software Engineer','545 Perry Street','New York','New York','500-1000','Meat','Single','2','5/1/2013','Medium','3','Female','25-30','No','cluster_4');");
				db.execSQL("insert into "+
						"rmreq values('Aashna','Baji','aashnabaji@gmail.com','5208381515','26','Software Engineer','5 Lexington Ave','New York','New York','500-1000','Meat','Single','2','5/1/2013','Medium','3','Male','25-30','Yes','cluster_4');");
				db.execSQL("insert into "+
						"rmreq values('Sadhana','Kannan','sadhanakannan@yahoo.com','5208381515','26','Financial Analyst','3 Central Park','New York','New York','Below 500','Veg','Single','2','5/1/2013','Ground','5','Male','30-40','No Preference','cluster_4');");
				db.execSQL("insert into "+
						"rmreq values('Pavitra','Balekai','pavi@gmail.com','5208381515','35','Financial Analyst','165 Park Ave','New York','New York','Below 500','No preference','Shared','2','5/1/2013','High','2','Male','30-40','No Preference','cluster_4');");
				db.execSQL("insert into "+
						"rmreq values('Prithvi','Balekai','prithvi@gmail.com','5208381515','32','Student','350 Townsend Street','San Diego','California','Below 500','Meat','Shared','2','5/1/2013','Ground','4','Female','18-25','Yes','cluster_5');");
				db.execSQL("insert into "+
						"rmreq values('Pavana','Ballal','pavana@yahoo.com','5208381515','40','Student','25 E Elm Street','Chicago','Illinois','1000-1500','Meat','Single','2','5/1/2013','Medium','2','Female','25-30','No','cluster_5');");
				db.execSQL("insert into "+
						"rmreq values('Michelle','Boros','mboros@gmail.com','5208381515','18','Student','125  Myra Way','San Diego','California','1000-1500','Veg','Shared','2','5/1/2013','Ground','5','Female','30-40','No Preference','cluster_5');");
				db.execSQL("insert into "+
						"rmreq values('Admin','mis','mis510@gmail.com','6193190059','23','Student','125  Myra Way','Tucson','California','1000-1500','Veg','Shared','2','5/1/2013','Ground','5','Female','30-40','No Preference','cluster_0');");
				//System.out.println("after insert");
				
			}

			@Override
			public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
			{
				Log.w(TAG, "Upgrading database from version " + oldVersion
						+ " to "
						+ newVersion + ", which will destroy all old data");
				db.execSQL("DROP TABLE IF EXISTS login");
				onCreate(db);
			}
		}    

		public void open() throws SQLException
		{
			db = DBHelper.getWritableDatabase();
		}

		public void close()
		{
			DBHelper.close();
		}    

		public long AddUser(String username, String password)
		{
			 ContentValues initialValues = new ContentValues();
			 initialValues.put(KEY_USERNAME, username);
			 initialValues.put(KEY_PASSWORD, password);
			 return db.insert(DATABASE_TABLE_LOGIN, null, initialValues);

		}

		public boolean Login(String username, String password) throws SQLException
		{
			Cursor mCursor = db.rawQuery("SELECT * FROM " + DATABASE_TABLE_LOGIN + " WHERE Username=? AND Password=?", new String[]{username,password});
			System.out.println("after query "+ mCursor.getCount()+"index: "+mCursor.getColumnIndex(KEY_PAID));
			if (mCursor != null && mCursor.getCount()>0) {
					mCursor.moveToFirst();
					String paid = mCursor.getString(mCursor.getColumnIndex(KEY_PAID));
					System.out.println(paid);
					if(paid.equals("true"))
						return true;
			}
		 return false;
		}
		
		public String Emailretrieve(String username) throws SQLException
		{
			Cursor mCursor = db.rawQuery("SELECT * FROM " + DATABASE_TABLE_LOGIN + " WHERE Username=?", new String[]{username});
			if (mCursor != null && mCursor.getCount()>0) {
					mCursor.moveToFirst();
					String email = mCursor.getString(mCursor.getColumnIndex(KEY_EMAIL));
					return email;
			}
			return null;
		}
		
		public ArrayList<String> Reqretrieve(String useremail) throws SQLException
		{
			//String email_temp = useremail;
			ArrayList<String> rm = new ArrayList<String>();
			rm_email.clear();
			Cursor mCursor = db.rawQuery("SELECT * FROM " + DATABASE_TABLE_RMREQ + " WHERE Email=?", new String[]{useremail});
			if (mCursor != null && mCursor.getCount()>0) {
					mCursor.moveToFirst();
					String cluster = mCursor.getString(mCursor.getColumnIndex(KEY_CLASS));
					String city = mCursor.getString(mCursor.getColumnIndex(KEY_CITY));
					System.out.println("user cluster city email "+cluster+city);
					Cursor mCursor_rm = db.rawQuery("SELECT * FROM " + DATABASE_TABLE_RMREQ+ " WHERE Class=? AND City=? AND Email!=?", new String[]{cluster,city,useremail});
					System.out.println("after filter"+mCursor_rm.getCount());
					if(mCursor_rm!=null && mCursor_rm.getCount()>0)
					{
						String rm_info;
						mCursor_rm.moveToFirst();
						do
						{
								rm_info = "Name: "+mCursor_rm.getString(0) + " " + mCursor_rm.getString(1) +"  Budget: " +mCursor_rm.getString(9) + "  Food Habit: " +mCursor_rm.getString(10)+"  Move-in: "+ mCursor_rm.getString(13) + "  Gender Preference: " +mCursor_rm.getString(16) + "  Preferred Age Group: " +mCursor_rm.getString(17) + "  Pet Preference: " +mCursor_rm.getString(18);
								rm.add(rm_info);
//								rm.add("");
								rm_email.add(mCursor_rm.getString(2));
//								rm_email.add("");
								
								System.out.println(rm_info);
								
						}while(mCursor_rm.moveToNext());
						System.out.println("finished addition");
//						for (int i = 0; i < rm.size(); i++) {
//			    		     System.out.println(rm.get(i));
//			    		}
						System.out.println("back to page");
						return rm;
					}
					else
					{
						rm.add("Currently no one matches your requirements, please come back and check later.");
						return rm;
					}
						
			}
			else
			{
				//System.out.println("new user no request");
				rm.add("You haven't submitted any roommate request yet.");
				return rm;
			}
		}
		
		public ArrayList<String> Emailsending(int position, String useremail_req) throws SQLException
		{
			String roommate_email = rm_email.get(position);
			System.out.println("email in db"+roommate_email);
			Cursor mCursor = db.rawQuery("SELECT * FROM "+ DATABASE_TABLE_RMREQ+" WHERE Email=?", new String[]{useremail_req});
			System.out.println(mCursor.getCount());
			if(mCursor!= null && mCursor.getCount()>0)
			{
				ArrayList<String> email_content = new ArrayList<String>();
				mCursor.moveToFirst();
				String receiver_email = roommate_email;
				email_content.add(receiver_email);
				String firstname = mCursor.getString(0);
				email_content.add(firstname);
				String lastname = mCursor.getString(1);
				email_content.add(lastname);
				System.out.println(lastname);
				String Emailaddress = mCursor.getString(2);
				email_content.add(Emailaddress);
				String phonenumber = mCursor.getString(3);
				email_content.add(phonenumber);
				String age = mCursor.getString(4);
				email_content.add(age);
				System.out.println(age);
				String profession = mCursor.getString(5);
				email_content.add(profession);
				String city = mCursor.getString(7);
				email_content.add(city);
				String budget = mCursor.getString(9);
				email_content.add(budget);
				System.out.println(budget);
				String foodhabits = mCursor.getString(10);
				email_content.add(foodhabits);
				String dateofjoin = mCursor.getString(13);
				email_content.add(dateofjoin);
				String gender = mCursor.getString(16);
				email_content.add(gender);
				System.out.println(gender);
				String agegroup = mCursor.getString(17);
				email_content.add(agegroup);
				String pets = mCursor.getString(18); 
				email_content.add(pets);
				System.out.println("#param "+email_content.size());
				//sendmail sending = new sendmail();
				//sending.SendEmail(receiver_email, firstname, lastname, Emailaddress, phonenumber, age, profession, city, budget, foodhabits, dateofjoin, gender, agegroup, pets);
				return email_content;
			}
			return null;	
		}
		
		
		
		public boolean Pwretrieve(String email) throws SQLException
		{
			Cursor mCursor = db.rawQuery("SELECT * FROM " + DATABASE_TABLE_LOGIN + " WHERE Email=?", new String[]{email});
			if (mCursor != null && mCursor.getCount()>0) {
					mCursor.moveToFirst();
					String paid = mCursor.getString(mCursor.getColumnIndex(KEY_PAID));
					if(paid.equals("true"))
						return true;
			}
		 return false;
		}

	}
